/**   
 * Copyright © 2019 eSunny Info. Tech Ltd. All rights reserved.
 * 
 * @Description:TODO(功能)
 * @className ProviderServiceImpl.java
 * @Package: com.erp.service 
 * @author: yj   
 * @date: 2019年1月17日 上午10:00:42 
 */
package com.erp.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.erp.dao.ProductsDaoImpl;
import com.erp.dao.ProviderDaoImpl;
import com.erp.po.Providers;
import com.erp.util.DBHelper;
import com.erp.util.Page;

/**
 * @className ProviderServiceImpl.java
 * @author yj
 * @Description:TODO(功能)
 * @Date 2019年1月17日
 */
public class ProviderServiceImpl implements ProviderService {

	private ProviderDaoImpl prod;
	private ProductsDaoImpl pd;
	
	public ProviderServiceImpl(){
		prod = new ProviderDaoImpl();
		pd = new ProductsDaoImpl();
	}

	/* (查询 ( 查询 Provider <供应商> 全部 )(分页))
	 * 
	 */
	@Override
	public Page<Providers> findProvidersListByPage(Page<Providers> page) {
		ArrayList<Providers> list = new ArrayList<Providers>();
		Connection conn = null;
		int total = 0;
		
		try {
			conn = DBHelper.getConnection();
			
			//查询 ( 总记录数 )
			ResultSet set2 = prod.selectProvidersByTotal(conn);
			while (set2.next()) {
				total = set2.getInt(1);
			}
			
			ResultSet set = prod.selectProvidersByPage(conn, page);
			while (set.next()) {
				int providerId = set.getInt("providerID");
				String providerName = set.getString("provider_name");
				String providerAdd = set.getString("provider_add");
				String providerTel = set.getString("provider_tel");
				String account = set.getString("account");
				String email = set.getString("email");
				
				list.add(new Providers(providerId, providerName, providerAdd, providerTel, account, email));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			prod.close();
		}
		
		page.setList(list);
		page.setTotal(total);
		
		return page;
	}

	/* (增加 ( 增加 供应商 )) 查询 (是否有添加重名的商品)
	 * 
	 */
	@Override
	public int addProviders(Providers providers) {
		Connection conn = null;
		boolean f = false;
		int code = 0; // 300: 有空串     |  301: 重复值       | 302: 成功     |  303: 没有该类别
		if (providers == null || providers.getProviderName().equals("") || providers.getProviderName().equals(" ")
				|| providers.getProviderAdd().equals("") || providers.getProviderAdd().equals(" ")
				|| providers.getProviderTel().equals("") || providers.getProviderTel().equals(" ")
				|| providers.getAccount().equals("") || providers.getAccount().equals(" ")
				|| providers.getEmail().equals("") || providers.getEmail().equals(" ")) {
			code = 300;
			return code;
		}
		
		
		try {
			conn = DBHelper.getConnection();
			
			// 查询 (是否有添加重名的商品)
			ResultSet set = prod.selectProvidersByInsert(conn, providers);
			if (set.next()) {
				// return false 添加失败
				//f = false;
				code = 301;
			} else {
				
				// 事务处理
				conn.setAutoCommit(false);
				int i = prod.insertProviders(conn, providers);
				conn.commit();
				conn.setAutoCommit(true);
				if (i > 0) {
					//f = true;
					code = 302;
				} else {
					//f = false;
					code = 303;
				}
			}
			
		} catch (SQLException e) {
			
			try {
				conn.rollback();
				conn.setAutoCommit(true);
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			prod.close();
		}
		
		return code;
	}

	/* (修改 (根据id 修改 供应商())  / 针对本 id 的名称不修改,不构成重名)
	 * 
	 */
	@Override
	public int modifyProvidersById(Providers providers) {
		Connection conn = null;
		//boolean f = false;
		int code = 0; // 300: 有空串     |  301: 重复值       | 302: 成功
		if (providers == null || providers.getProviderName().equals("") || providers.getProviderName().equals(" ")
				|| providers.getProviderAdd().equals("") || providers.getProviderAdd().equals(" ")
				|| providers.getProviderTel().equals("") || providers.getProviderTel().equals(" ")
				|| providers.getAccount().equals("") || providers.getAccount().equals(" ")
				|| providers.getEmail().equals("") || providers.getEmail().equals(" ")) {
			code = 300;
			return code;
		}
		
		try {
			conn = DBHelper.getConnection();
			
			// 查询 (针对本 id 的名称不修改,不构成重名)
			ResultSet set = prod.selectProvidersByInsert(conn, providers);
			if (set.next()) {

				int findId = set.getInt(1);
				if (!(providers.getProviderId() == findId)) {
					code = 301;
					return code;
				}
			}
			
			//事务处理
			conn.setAutoCommit(false);
			int i = prod.updateProvidersById(conn, providers);
			conn.commit();
			conn.setAutoCommit(true);
			if (i > 0) {
				//f = true;
				code = 302;
			} else {
				//f = false;
				code = 303;
			}
			
		} catch (SQLException e) {
			
			try {
				conn.rollback();
				conn.setAutoCommit(true);
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			prod.close();
		}

		return code;
	}

	/* (删除 ( 删除 供应商 ) -- 外键校验)
	 *
	 */
	@Override
	public int removeProvidersById(int providerId) {
		Connection conn = null;
		//boolean f = false;
		int num = 0;
		int code = 0;   // 300: 商品不为空        |  301: 没有该供应商      |  302: 成功
		// 事务处理
		try {
			conn = DBHelper.getConnection();

			// -- 外键校验
			ResultSet set = pd.selectProvidersByFkCheck(conn, providerId);
			while (set.next()) {
				num = set.getInt(1);

				if (num > 0) {
					code = 300;
					//f = false;
				} else {
					
					//事务处理
					conn.setAutoCommit(false);
					int i = prod.deleteProvidersById(conn, providerId);
					conn.commit();
					conn.setAutoCommit(true);
					if (i > 0) {
						code = 302;
						//f = true;
					} else {
						code = 301;
						//f = false;
					}
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			prod.close();
		}

		return code;
	}

	/* (查询 Provider <供应商> 全部)
	 * 
	 */
	@Override
	public List<Providers> findProviders() {

		ArrayList<Providers> list = new ArrayList<Providers>();
		Connection conn = null;
		try {
			conn = DBHelper.getConnection();
			ResultSet set = prod.selectProviders(conn);
			while(set.next()) {
				int providerId = set.getInt(1);
				String providerName = set.getString("provider_name");
				String providerAdd = set.getString("provider_add");
				String providerTel = set.getString("provider_tel");
				String account = set.getString("account");
				String email = set.getString("email");
				
				list.add(new Providers(providerId, providerName, providerAdd, providerTel, account, email));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			prod.close();
		}
		
		return list;
	}

	/* (根据id查询 Provider)
	 * 
	 */
	@Override
	public Providers findProviderById(int id) {
		Connection conn = null;
		ResultSet set = null;
		
		try {
			conn = DBHelper.getConnection();
			set = prod.selectProviderById(conn, id);
			if (set.next()) {
				int providerId = set.getInt(1);
				String providerName = set.getString("provider_name");
				String providerAdd = set.getString("provider_add");
				String providerTel = set.getString("provider_tel");
				String account = set.getString("account");
				String email = set.getString("email");
				return new Providers(providerId, providerName, providerAdd, providerTel, account, email);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			prod.close();
		}
		return null;
	}

	/**
	 * 根据名称查询记录
	 */
	public boolean findProviderByName(String providName) {
		Connection conn = null;
		ResultSet set = null;
		try {
			conn = DBHelper.getConnection();
			
			set = prod.selectProviderByName(conn, providName);
			if(set.next()) {
				return false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			prod.close();
		}
		
		return true;
	}

	/**
	 * id相同,不修改名称不构成重名
	 */
	public Providers findProviderByPName(String providName) {
		Connection conn = null;
		ResultSet set = null;
		try {
			conn = DBHelper.getConnection();
			
			set = prod.selectProviderByName(conn, providName);
			if(set.next()) {
				int providerId = set.getInt("providerID");
				
				return new Providers(providerId);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			prod.close();
		}
		
		return null;
	}

	/* 
	 * 根据条件查询 ( 根据条件查询 Provider <供应商> 全部 )(分页)
	 */
	@Override
	public Page<Providers> findProvidersListByCondition(Page<Providers> page, Providers pro) {
		ArrayList<Providers> list = new ArrayList<Providers>();
		Connection conn = null;
		int total = 0;
		
		try {
			conn = DBHelper.getConnection();
			
			//查询 ( 总记录数 )
			ResultSet set2 = prod.selectConditionByTotal(conn, pro);
			while (set2.next()) {
				total = set2.getInt(1);
			}
			
			ResultSet set = prod.selectProvidersByCondition(conn, pro, page);
			while (set.next()) {
				int providerId = set.getInt("providerID");
				String providerName = set.getString("provider_name");
				String providerAdd = set.getString("provider_add");
				String providerTel = set.getString("provider_tel");
				String account = set.getString("account");
				String email = set.getString("email");
				
				list.add(new Providers(providerId, providerName, providerAdd, providerTel, account, email));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			prod.close();
		}
		
		page.setList(list);
		page.setTotal(total);
		
		return page;
	}

	
	
	
	
	
	
}
